package com.morningstar.qs.api.basic.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * utility related to excel operation
 * 
 * @author bguo
 */
public class ExcelUtil {
	
	private final static Logger gLogger = Logger.getLogger(ExcelUtil.class);
	
	public static final int EXCEL_FORMAT_2003 = 1;
	public static final int EXCEL_FORMAT_2007 = 2;
	

	/**
	 * parse the excel, then return the corresponding String array.
	 * @param in  input stream
	 * @param sheetIndex  decide which sheet to be parsed. 
	 * 					if less than zero, the first sheet will be parsed.
	 * 					if larger than real sheet last index, the last sheet will be parsed.
	 * @param format    specify the excel format. Currently we support 2003 and 2007 format.
	 * @return
	 */
	public static String[][] parseExcel(InputStream in, int sheetIndex, int format)
	{
			Workbook srcWb = null;
			
			try
			{
				//decide the excel format
				if (EXCEL_FORMAT_2003 == format)
				{
					srcWb = new HSSFWorkbook(in);
				}
				else if (EXCEL_FORMAT_2007 == format)
				{
					srcWb = new XSSFWorkbook(in);
				}
				
			} catch (Exception e)
			{
				e.printStackTrace();
				return null;
			}
			
			//decide the sheet index
			if (sheetIndex < 0)
			{
				sheetIndex = 0;
			}
			else if (sheetIndex > srcWb.getNumberOfSheets()-1)
			{
				sheetIndex = srcWb.getNumberOfSheets()-1;
			}
			
			Sheet sheet = srcWb.getSheetAt(sheetIndex);
			if (null == sheet)
			{
				gLogger.info("No sheet for index [" + sheetIndex + "] in this excel file");
				throw new RuntimeException("No valid sheet is found in the excel file.");
			}
			
			int rowCount = sheet.getLastRowNum();
			short columnCount = sheet.getRow(1).getLastCellNum();

			if (rowCount <= 0) //if no row
			{
				gLogger.info("No row in this excel file");
				throw new RuntimeException("No data is found in the excel file.");
			}

			if (columnCount <= 0) //if no column
			{
				gLogger.info("No column in this excel file");
				throw new RuntimeException("No data is found in the excel file.");
			}
			
			String[][] result = new String[rowCount+1][columnCount];
			
			DateFormat df = new SimpleDateFormat("MM-dd-yyyy");
			
			for (int currentRowNum = 0; currentRowNum <= rowCount; currentRowNum++) { //loop row
				
				Row row = sheet.getRow(currentRowNum); //current row
				
				if (null == row)
				{
					continue;
				}
				
				for (short currentCellNum = 0; currentCellNum < row.getLastCellNum(); currentCellNum++) { //loop column
					
					Cell cell = row.getCell(currentCellNum); //current cell
					
					if (null != cell) {
						
						if (Cell.CELL_TYPE_STRING == cell.getCellType())
						{
							result[currentRowNum][currentCellNum] = cell.getStringCellValue();
						}
						else if (Cell.CELL_TYPE_BLANK == cell.getCellType())
						{
							result[currentRowNum][currentCellNum] = "";
						}
						else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType())
						{
							if (DateUtil.isCellDateFormatted(cell))  //if it's a date
							{
								result[currentRowNum][currentCellNum] = df.format(cell.getDateCellValue());
							}
							else
							{
								result[currentRowNum][currentCellNum] = "" + cell.getNumericCellValue();
							}
						}
						else if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType())
						{
							result[currentRowNum][currentCellNum] = "" + cell.getBooleanCellValue();
						}
						else
						{
							result[currentRowNum][currentCellNum] = "";
						}
					}
				}
			}
			
			return result;
	}


	/**
	 * save HSSFWorkbook into an excel file
	 * @param resultWb
	 * @param fileName
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static void saveFile(HSSFWorkbook resultWb, String fileName)
			throws FileNotFoundException, IOException {
		FileOutputStream fileOut = new FileOutputStream(fileName);
		resultWb.write(fileOut);
		fileOut.flush();
		fileOut.close();
	}

	/**
	 * copy rows from source sheet to target sheet at specific position.
	 * @param sourceSheet
	 * @param targetSheet
	 * @param pStartRow  start row of source sheet
	 * @param pEndRow    end row of source sheet
	 * @param pPosition  the inserted position of target sheet
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static HSSFRow copyRows(HSSFSheet sourceSheet, HSSFSheet targetSheet,
			int pStartRow, int pEndRow, int pPosition) {
		HSSFRow sourceRow = null;
		HSSFRow targetRow = null;
		HSSFCell sourceCell = null;
		HSSFCell targetCell = null;
		
		// HSSFSheet sourceSheet = null;
		// HSSFSheet targetSheet = null;
		
		Region region = null;
		int cType;
		int i;
		short j;
		int targetRowFrom;
		int targetRowTo;

		if ((pStartRow == -1) || (pEndRow == -1)) {
			return targetRow;
		}
		// sourceSheet = wb.getSheet(pSourceSheetName);
		// targetSheet = wb.getSheet(pTargetSheetName);

		//copy merged cells
		for (i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
			region = sourceSheet.getMergedRegionAt(i);
			if ((region.getRowFrom() >= pStartRow)
					&& (region.getRowTo() <= pEndRow)) {
				targetRowFrom = region.getRowFrom() - pStartRow + pPosition;
				targetRowTo = region.getRowTo() - pStartRow + pPosition;
				region.setRowFrom(targetRowFrom);
				region.setRowTo(targetRowTo);
				targetSheet.addMergedRegion(region);
			}
		}
		
		//set width of column
		for (i = pStartRow; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow != null) {
				for (j = sourceRow.getLastCellNum(); j > sourceRow
						.getFirstCellNum(); j--) {
					targetSheet
							.setColumnWidth(j, sourceSheet.getColumnWidth(j));
					targetSheet.setColumnHidden(j, false);
				}
				break;
			}
		}
		
		//copy rows
		for (; i <= pEndRow; i++) {
			sourceRow = sourceSheet.getRow(i);
			if (sourceRow == null) {
				continue;
			}
			targetRow = targetSheet.createRow(i - pStartRow + pPosition);
			targetRow.setHeight(sourceRow.getHeight());
			for (j = sourceRow.getFirstCellNum(); j < sourceRow
					.getLastCellNum(); j++) {
				sourceCell = sourceRow.getCell(j);
				if (sourceCell == null) {
					continue;
				}
				targetCell = targetRow.createCell(j);
				//targetCell.setEncoding(sourceCell.getEncoding());
				targetCell.setCellStyle(sourceCell.getCellStyle());
				cType = sourceCell.getCellType();
				targetCell.setCellType(cType);
				switch (cType) {
				case HSSFCell.CELL_TYPE_BOOLEAN:
					targetCell.setCellValue(sourceCell.getBooleanCellValue());
					break;
				case HSSFCell.CELL_TYPE_ERROR:
					targetCell
							.setCellErrorValue(sourceCell.getErrorCellValue());
					break;
				case HSSFCell.CELL_TYPE_FORMULA:
					
					targetCell.setCellFormula(parseFormula(sourceCell
							.getCellFormula()));
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					targetCell.setCellValue(sourceCell.getNumericCellValue());
					break;
				case HSSFCell.CELL_TYPE_STRING:
					targetCell
							.setCellValue(sourceCell.getRichStringCellValue());
					break;
				}

			}

		}
		return targetRow;

	}

	/**
	 * get an InputStream for the given file path
	 * Note that it's the invoker's responsibility to close this input stream !
	 * @param file
	 * @return InputStream
	 */
	public static InputStream getInputStream(String file) {
		InputStream in = null;
		
		//try to load from current disk path 
		try {
			in = new FileInputStream(file);
			return in;
		} catch (FileNotFoundException e) {
			in = null;
		}

		//try to load from class path
		ClassLoader classLoader = Thread.currentThread().getContextClassLoader();
		if (classLoader == null) {
			classLoader = ExcelUtil.class.getClassLoader();
		}
		in = classLoader.getResourceAsStream(file);
		
		return in;
	}
	
	private static String parseFormula(String pPOIFormula) {
		final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
		StringBuffer result = null;
		int index;

		result = new StringBuffer();
		index = pPOIFormula.indexOf(cstReplaceString);
		if (index >= 0) {
			result.append(pPOIFormula.substring(0, index));
			result.append(pPOIFormula.substring(index
					+ cstReplaceString.length()));
		} else {
			result.append(pPOIFormula);
		}

		return result.toString();
	}

	
	public static void main(String[] args) {
		try {
			
			String [][] result = ExcelUtil.parseExcel(new FileInputStream("Excel Template.xlsx"), 1, ExcelUtil.EXCEL_FORMAT_2007);
			for (String[] row : result)
			{
				for (String col : row)
				{
					System.out.print(col);
					System.out.print("  ");
					System.out.println();
				}
				System.out.println("------------------");
			}
			
			
//			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
//					"src.xls"));
//			HSSFWorkbook srcWb = new HSSFWorkbook(fs);
//			HSSFWorkbook resultWb = new HSSFWorkbook();
//
//			copyRows(srcWb.getSheetAt(0), resultWb.createSheet(), 0, 3, 0);
//			saveFile(resultWb, "result.xls");
			
//			System.out.println("Operation finished");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
